{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 134,
   "metadata": {},
   "outputs": [],
   "source": [
    "import numpy as np \n",
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 183,
   "metadata": {},
   "outputs": [],
   "source": [
    "trips = pd.read_csv('google_transit_dublinbus/trips.txt',sep=',')\n",
    "stop_times = pd.read_csv('google_transit_dublinbus/stop_times.txt',sep=',')\n",
    "routes = pd.read_csv('google_transit_dublinbus/routes.txt',sep=',')\n",
    "calendar = pd.read_csv('google_transit_dublinbus/calendar.txt',sep=',')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 136,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>route_id</th>\n",
       "      <th>service_id</th>\n",
       "      <th>trip_id</th>\n",
       "      <th>shape_id</th>\n",
       "      <th>trip_headsign</th>\n",
       "      <th>direction_id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>60-11-b12-1</td>\n",
       "      <td>1</td>\n",
       "      <td>3271.1.60-11-b12-1.16.I</td>\n",
       "      <td>60-11-b12-1.16.I</td>\n",
       "      <td>Blackthorn Road - Saint Pappin's Road</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>60-11-b12-1</td>\n",
       "      <td>1</td>\n",
       "      <td>3272.1.60-11-b12-1.15.O</td>\n",
       "      <td>60-11-b12-1.15.O</td>\n",
       "      <td>Saint Pappin's Road - Blackthorn Drive (Blackt...</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>60-11-b12-1</td>\n",
       "      <td>1</td>\n",
       "      <td>3273.1.60-11-b12-1.16.I</td>\n",
       "      <td>60-11-b12-1.16.I</td>\n",
       "      <td>Blackthorn Road - Saint Pappin's Road</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>60-11-b12-1</td>\n",
       "      <td>1</td>\n",
       "      <td>3274.1.60-11-b12-1.15.O</td>\n",
       "      <td>60-11-b12-1.15.O</td>\n",
       "      <td>Saint Pappin's Road - Blackthorn Drive (Blackt...</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>60-11-b12-1</td>\n",
       "      <td>1</td>\n",
       "      <td>3275.1.60-11-b12-1.16.I</td>\n",
       "      <td>60-11-b12-1.16.I</td>\n",
       "      <td>Blackthorn Road - Saint Pappin's Road</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      route_id service_id                  trip_id          shape_id  \\\n",
       "0  60-11-b12-1          1  3271.1.60-11-b12-1.16.I  60-11-b12-1.16.I   \n",
       "1  60-11-b12-1          1  3272.1.60-11-b12-1.15.O  60-11-b12-1.15.O   \n",
       "2  60-11-b12-1          1  3273.1.60-11-b12-1.16.I  60-11-b12-1.16.I   \n",
       "3  60-11-b12-1          1  3274.1.60-11-b12-1.15.O  60-11-b12-1.15.O   \n",
       "4  60-11-b12-1          1  3275.1.60-11-b12-1.16.I  60-11-b12-1.16.I   \n",
       "\n",
       "                                       trip_headsign  direction_id  \n",
       "0              Blackthorn Road - Saint Pappin's Road             1  \n",
       "1  Saint Pappin's Road - Blackthorn Drive (Blackt...             0  \n",
       "2              Blackthorn Road - Saint Pappin's Road             1  \n",
       "3  Saint Pappin's Road - Blackthorn Drive (Blackt...             0  \n",
       "4              Blackthorn Road - Saint Pappin's Road             1  "
      ]
     },
     "execution_count": 136,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "trips.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 137,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>trip_id</th>\n",
       "      <th>arrival_time</th>\n",
       "      <th>departure_time</th>\n",
       "      <th>stop_id</th>\n",
       "      <th>stop_sequence</th>\n",
       "      <th>stop_headsign</th>\n",
       "      <th>pickup_type</th>\n",
       "      <th>drop_off_type</th>\n",
       "      <th>shape_dist_traveled</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1.1.60-39A-b12-1.205.I</td>\n",
       "      <td>07:55:00</td>\n",
       "      <td>07:55:00</td>\n",
       "      <td>8240DB007158</td>\n",
       "      <td>1</td>\n",
       "      <td>UCD</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1.1.60-39A-b12-1.205.I</td>\n",
       "      <td>07:55:43</td>\n",
       "      <td>07:55:43</td>\n",
       "      <td>8240DB007048</td>\n",
       "      <td>2</td>\n",
       "      <td>UCD</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>750698.41</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1.1.60-39A-b12-1.205.I</td>\n",
       "      <td>07:56:27</td>\n",
       "      <td>07:56:27</td>\n",
       "      <td>8240DB007699</td>\n",
       "      <td>3</td>\n",
       "      <td>UCD</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1501239.38</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1.1.60-39A-b12-1.205.I</td>\n",
       "      <td>07:56:54</td>\n",
       "      <td>07:56:54</td>\n",
       "      <td>8240DB007159</td>\n",
       "      <td>4</td>\n",
       "      <td>UCD</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2251660.48</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1.1.60-39A-b12-1.205.I</td>\n",
       "      <td>07:57:13</td>\n",
       "      <td>07:57:13</td>\n",
       "      <td>8240DB007388</td>\n",
       "      <td>5</td>\n",
       "      <td>UCD</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>3001993.17</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                  trip_id arrival_time departure_time       stop_id  \\\n",
       "0  1.1.60-39A-b12-1.205.I     07:55:00       07:55:00  8240DB007158   \n",
       "1  1.1.60-39A-b12-1.205.I     07:55:43       07:55:43  8240DB007048   \n",
       "2  1.1.60-39A-b12-1.205.I     07:56:27       07:56:27  8240DB007699   \n",
       "3  1.1.60-39A-b12-1.205.I     07:56:54       07:56:54  8240DB007159   \n",
       "4  1.1.60-39A-b12-1.205.I     07:57:13       07:57:13  8240DB007388   \n",
       "\n",
       "   stop_sequence stop_headsign  pickup_type  drop_off_type  \\\n",
       "0              1           UCD            0              0   \n",
       "1              2           UCD            0              0   \n",
       "2              3           UCD            0              0   \n",
       "3              4           UCD            0              0   \n",
       "4              5           UCD            0              0   \n",
       "\n",
       "   shape_dist_traveled  \n",
       "0                 0.00  \n",
       "1            750698.41  \n",
       "2           1501239.38  \n",
       "3           2251660.48  \n",
       "4           3001993.17  "
      ]
     },
     "execution_count": 137,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "stop_times.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 138,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>route_id</th>\n",
       "      <th>agency_id</th>\n",
       "      <th>route_short_name</th>\n",
       "      <th>route_long_name</th>\n",
       "      <th>route_type</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>60-1-b12-1</td>\n",
       "      <td>978</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>60-102-b12-1</td>\n",
       "      <td>978</td>\n",
       "      <td>102</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>60-104-b12-1</td>\n",
       "      <td>978</td>\n",
       "      <td>104</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>60-11-b12-1</td>\n",
       "      <td>978</td>\n",
       "      <td>11</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>60-111-b12-1</td>\n",
       "      <td>978</td>\n",
       "      <td>111</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       route_id  agency_id route_short_name  route_long_name  route_type\n",
       "0    60-1-b12-1        978                1              NaN           3\n",
       "1  60-102-b12-1        978              102              NaN           3\n",
       "2  60-104-b12-1        978              104              NaN           3\n",
       "3   60-11-b12-1        978               11              NaN           3\n",
       "4  60-111-b12-1        978              111              NaN           3"
      ]
     },
     "execution_count": 138,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "routes.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 184,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>service_id</th>\n",
       "      <th>monday</th>\n",
       "      <th>tuesday</th>\n",
       "      <th>wednesday</th>\n",
       "      <th>thursday</th>\n",
       "      <th>friday</th>\n",
       "      <th>saturday</th>\n",
       "      <th>sunday</th>\n",
       "      <th>start_date</th>\n",
       "      <th>end_date</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>20200715</td>\n",
       "      <td>20200718</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>20200715</td>\n",
       "      <td>20200718</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>y1001</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>20200719</td>\n",
       "      <td>20200815</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>y1002</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>20200719</td>\n",
       "      <td>20200815</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>y1003</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>20200719</td>\n",
       "      <td>20200815</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  service_id  monday  tuesday  wednesday  thursday  friday  saturday  sunday  \\\n",
       "0          1       0        0          1         1       1         0       0   \n",
       "1          3       0        0          0         0       0         1       0   \n",
       "2      y1001       0        0          0         0       0         1       0   \n",
       "3      y1002       1        1          1         1       1         0       0   \n",
       "4      y1003       1        0          0         0       0         0       1   \n",
       "\n",
       "   start_date  end_date  \n",
       "0    20200715  20200718  \n",
       "1    20200715  20200718  \n",
       "2    20200719  20200815  \n",
       "3    20200719  20200815  \n",
       "4    20200719  20200815  "
      ]
     },
     "execution_count": 184,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "calendar"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 139,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = stop_times.merge(trips,on='trip_id')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 140,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>trip_id</th>\n",
       "      <th>arrival_time</th>\n",
       "      <th>departure_time</th>\n",
       "      <th>stop_id</th>\n",
       "      <th>stop_sequence</th>\n",
       "      <th>stop_headsign</th>\n",
       "      <th>pickup_type</th>\n",
       "      <th>drop_off_type</th>\n",
       "      <th>shape_dist_traveled</th>\n",
       "      <th>route_id</th>\n",
       "      <th>service_id</th>\n",
       "      <th>shape_id</th>\n",
       "      <th>trip_headsign</th>\n",
       "      <th>direction_id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1.1.60-39A-b12-1.205.I</td>\n",
       "      <td>07:55:00</td>\n",
       "      <td>07:55:00</td>\n",
       "      <td>8240DB007158</td>\n",
       "      <td>1</td>\n",
       "      <td>UCD</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.00</td>\n",
       "      <td>60-39A-b12-1</td>\n",
       "      <td>1</td>\n",
       "      <td>60-39A-b12-1.205.I</td>\n",
       "      <td>Delhurst Estate - University College Dublin</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1.1.60-39A-b12-1.205.I</td>\n",
       "      <td>07:55:43</td>\n",
       "      <td>07:55:43</td>\n",
       "      <td>8240DB007048</td>\n",
       "      <td>2</td>\n",
       "      <td>UCD</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>750698.41</td>\n",
       "      <td>60-39A-b12-1</td>\n",
       "      <td>1</td>\n",
       "      <td>60-39A-b12-1.205.I</td>\n",
       "      <td>Delhurst Estate - University College Dublin</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1.1.60-39A-b12-1.205.I</td>\n",
       "      <td>07:56:27</td>\n",
       "      <td>07:56:27</td>\n",
       "      <td>8240DB007699</td>\n",
       "      <td>3</td>\n",
       "      <td>UCD</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1501239.38</td>\n",
       "      <td>60-39A-b12-1</td>\n",
       "      <td>1</td>\n",
       "      <td>60-39A-b12-1.205.I</td>\n",
       "      <td>Delhurst Estate - University College Dublin</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1.1.60-39A-b12-1.205.I</td>\n",
       "      <td>07:56:54</td>\n",
       "      <td>07:56:54</td>\n",
       "      <td>8240DB007159</td>\n",
       "      <td>4</td>\n",
       "      <td>UCD</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2251660.48</td>\n",
       "      <td>60-39A-b12-1</td>\n",
       "      <td>1</td>\n",
       "      <td>60-39A-b12-1.205.I</td>\n",
       "      <td>Delhurst Estate - University College Dublin</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1.1.60-39A-b12-1.205.I</td>\n",
       "      <td>07:57:13</td>\n",
       "      <td>07:57:13</td>\n",
       "      <td>8240DB007388</td>\n",
       "      <td>5</td>\n",
       "      <td>UCD</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>3001993.17</td>\n",
       "      <td>60-39A-b12-1</td>\n",
       "      <td>1</td>\n",
       "      <td>60-39A-b12-1.205.I</td>\n",
       "      <td>Delhurst Estate - University College Dublin</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                  trip_id arrival_time departure_time       stop_id  \\\n",
       "0  1.1.60-39A-b12-1.205.I     07:55:00       07:55:00  8240DB007158   \n",
       "1  1.1.60-39A-b12-1.205.I     07:55:43       07:55:43  8240DB007048   \n",
       "2  1.1.60-39A-b12-1.205.I     07:56:27       07:56:27  8240DB007699   \n",
       "3  1.1.60-39A-b12-1.205.I     07:56:54       07:56:54  8240DB007159   \n",
       "4  1.1.60-39A-b12-1.205.I     07:57:13       07:57:13  8240DB007388   \n",
       "\n",
       "   stop_sequence stop_headsign  pickup_type  drop_off_type  \\\n",
       "0              1           UCD            0              0   \n",
       "1              2           UCD            0              0   \n",
       "2              3           UCD            0              0   \n",
       "3              4           UCD            0              0   \n",
       "4              5           UCD            0              0   \n",
       "\n",
       "   shape_dist_traveled      route_id service_id            shape_id  \\\n",
       "0                 0.00  60-39A-b12-1          1  60-39A-b12-1.205.I   \n",
       "1            750698.41  60-39A-b12-1          1  60-39A-b12-1.205.I   \n",
       "2           1501239.38  60-39A-b12-1          1  60-39A-b12-1.205.I   \n",
       "3           2251660.48  60-39A-b12-1          1  60-39A-b12-1.205.I   \n",
       "4           3001993.17  60-39A-b12-1          1  60-39A-b12-1.205.I   \n",
       "\n",
       "                                 trip_headsign  direction_id  \n",
       "0  Delhurst Estate - University College Dublin             1  \n",
       "1  Delhurst Estate - University College Dublin             1  \n",
       "2  Delhurst Estate - University College Dublin             1  \n",
       "3  Delhurst Estate - University College Dublin             1  \n",
       "4  Delhurst Estate - University College Dublin             1  "
      ]
     },
     "execution_count": 140,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 141,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = df.merge(routes,on='route_id')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 142,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>trip_id</th>\n",
       "      <th>arrival_time</th>\n",
       "      <th>departure_time</th>\n",
       "      <th>stop_id</th>\n",
       "      <th>stop_sequence</th>\n",
       "      <th>stop_headsign</th>\n",
       "      <th>pickup_type</th>\n",
       "      <th>drop_off_type</th>\n",
       "      <th>shape_dist_traveled</th>\n",
       "      <th>route_id</th>\n",
       "      <th>service_id</th>\n",
       "      <th>shape_id</th>\n",
       "      <th>trip_headsign</th>\n",
       "      <th>direction_id</th>\n",
       "      <th>agency_id</th>\n",
       "      <th>route_short_name</th>\n",
       "      <th>route_long_name</th>\n",
       "      <th>route_type</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1.1.60-39A-b12-1.205.I</td>\n",
       "      <td>07:55:00</td>\n",
       "      <td>07:55:00</td>\n",
       "      <td>8240DB007158</td>\n",
       "      <td>1</td>\n",
       "      <td>UCD</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.00</td>\n",
       "      <td>60-39A-b12-1</td>\n",
       "      <td>1</td>\n",
       "      <td>60-39A-b12-1.205.I</td>\n",
       "      <td>Delhurst Estate - University College Dublin</td>\n",
       "      <td>1</td>\n",
       "      <td>978</td>\n",
       "      <td>39a</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1.1.60-39A-b12-1.205.I</td>\n",
       "      <td>07:55:43</td>\n",
       "      <td>07:55:43</td>\n",
       "      <td>8240DB007048</td>\n",
       "      <td>2</td>\n",
       "      <td>UCD</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>750698.41</td>\n",
       "      <td>60-39A-b12-1</td>\n",
       "      <td>1</td>\n",
       "      <td>60-39A-b12-1.205.I</td>\n",
       "      <td>Delhurst Estate - University College Dublin</td>\n",
       "      <td>1</td>\n",
       "      <td>978</td>\n",
       "      <td>39a</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1.1.60-39A-b12-1.205.I</td>\n",
       "      <td>07:56:27</td>\n",
       "      <td>07:56:27</td>\n",
       "      <td>8240DB007699</td>\n",
       "      <td>3</td>\n",
       "      <td>UCD</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1501239.38</td>\n",
       "      <td>60-39A-b12-1</td>\n",
       "      <td>1</td>\n",
       "      <td>60-39A-b12-1.205.I</td>\n",
       "      <td>Delhurst Estate - University College Dublin</td>\n",
       "      <td>1</td>\n",
       "      <td>978</td>\n",
       "      <td>39a</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1.1.60-39A-b12-1.205.I</td>\n",
       "      <td>07:56:54</td>\n",
       "      <td>07:56:54</td>\n",
       "      <td>8240DB007159</td>\n",
       "      <td>4</td>\n",
       "      <td>UCD</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2251660.48</td>\n",
       "      <td>60-39A-b12-1</td>\n",
       "      <td>1</td>\n",
       "      <td>60-39A-b12-1.205.I</td>\n",
       "      <td>Delhurst Estate - University College Dublin</td>\n",
       "      <td>1</td>\n",
       "      <td>978</td>\n",
       "      <td>39a</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1.1.60-39A-b12-1.205.I</td>\n",
       "      <td>07:57:13</td>\n",
       "      <td>07:57:13</td>\n",
       "      <td>8240DB007388</td>\n",
       "      <td>5</td>\n",
       "      <td>UCD</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>3001993.17</td>\n",
       "      <td>60-39A-b12-1</td>\n",
       "      <td>1</td>\n",
       "      <td>60-39A-b12-1.205.I</td>\n",
       "      <td>Delhurst Estate - University College Dublin</td>\n",
       "      <td>1</td>\n",
       "      <td>978</td>\n",
       "      <td>39a</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                  trip_id arrival_time departure_time       stop_id  \\\n",
       "0  1.1.60-39A-b12-1.205.I     07:55:00       07:55:00  8240DB007158   \n",
       "1  1.1.60-39A-b12-1.205.I     07:55:43       07:55:43  8240DB007048   \n",
       "2  1.1.60-39A-b12-1.205.I     07:56:27       07:56:27  8240DB007699   \n",
       "3  1.1.60-39A-b12-1.205.I     07:56:54       07:56:54  8240DB007159   \n",
       "4  1.1.60-39A-b12-1.205.I     07:57:13       07:57:13  8240DB007388   \n",
       "\n",
       "   stop_sequence stop_headsign  pickup_type  drop_off_type  \\\n",
       "0              1           UCD            0              0   \n",
       "1              2           UCD            0              0   \n",
       "2              3           UCD            0              0   \n",
       "3              4           UCD            0              0   \n",
       "4              5           UCD            0              0   \n",
       "\n",
       "   shape_dist_traveled      route_id service_id            shape_id  \\\n",
       "0                 0.00  60-39A-b12-1          1  60-39A-b12-1.205.I   \n",
       "1            750698.41  60-39A-b12-1          1  60-39A-b12-1.205.I   \n",
       "2           1501239.38  60-39A-b12-1          1  60-39A-b12-1.205.I   \n",
       "3           2251660.48  60-39A-b12-1          1  60-39A-b12-1.205.I   \n",
       "4           3001993.17  60-39A-b12-1          1  60-39A-b12-1.205.I   \n",
       "\n",
       "                                 trip_headsign  direction_id  agency_id  \\\n",
       "0  Delhurst Estate - University College Dublin             1        978   \n",
       "1  Delhurst Estate - University College Dublin             1        978   \n",
       "2  Delhurst Estate - University College Dublin             1        978   \n",
       "3  Delhurst Estate - University College Dublin             1        978   \n",
       "4  Delhurst Estate - University College Dublin             1        978   \n",
       "\n",
       "  route_short_name  route_long_name  route_type  \n",
       "0              39a              NaN           3  \n",
       "1              39a              NaN           3  \n",
       "2              39a              NaN           3  \n",
       "3              39a              NaN           3  \n",
       "4              39a              NaN           3  "
      ]
     },
     "execution_count": 142,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 143,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "trip_id                 object\n",
       "arrival_time            object\n",
       "departure_time          object\n",
       "stop_id                 object\n",
       "stop_sequence            int64\n",
       "stop_headsign           object\n",
       "pickup_type              int64\n",
       "drop_off_type            int64\n",
       "shape_dist_traveled    float64\n",
       "route_id                object\n",
       "service_id              object\n",
       "shape_id                object\n",
       "trip_headsign           object\n",
       "direction_id             int64\n",
       "agency_id                int64\n",
       "route_short_name        object\n",
       "route_long_name        float64\n",
       "route_type               int64\n",
       "dtype: object"
      ]
     },
     "execution_count": 143,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.dtypes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 144,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.drop(['stop_headsign','pickup_type','drop_off_type','shape_dist_traveled','trip_headsign','agency_id','route_long_name','route_type'],inplace=True,axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 145,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>trip_id</th>\n",
       "      <th>arrival_time</th>\n",
       "      <th>departure_time</th>\n",
       "      <th>stop_id</th>\n",
       "      <th>stop_sequence</th>\n",
       "      <th>route_id</th>\n",
       "      <th>service_id</th>\n",
       "      <th>shape_id</th>\n",
       "      <th>direction_id</th>\n",
       "      <th>route_short_name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1.1.60-39A-b12-1.205.I</td>\n",
       "      <td>07:55:00</td>\n",
       "      <td>07:55:00</td>\n",
       "      <td>8240DB007158</td>\n",
       "      <td>1</td>\n",
       "      <td>60-39A-b12-1</td>\n",
       "      <td>1</td>\n",
       "      <td>60-39A-b12-1.205.I</td>\n",
       "      <td>1</td>\n",
       "      <td>39a</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1.1.60-39A-b12-1.205.I</td>\n",
       "      <td>07:55:43</td>\n",
       "      <td>07:55:43</td>\n",
       "      <td>8240DB007048</td>\n",
       "      <td>2</td>\n",
       "      <td>60-39A-b12-1</td>\n",
       "      <td>1</td>\n",
       "      <td>60-39A-b12-1.205.I</td>\n",
       "      <td>1</td>\n",
       "      <td>39a</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1.1.60-39A-b12-1.205.I</td>\n",
       "      <td>07:56:27</td>\n",
       "      <td>07:56:27</td>\n",
       "      <td>8240DB007699</td>\n",
       "      <td>3</td>\n",
       "      <td>60-39A-b12-1</td>\n",
       "      <td>1</td>\n",
       "      <td>60-39A-b12-1.205.I</td>\n",
       "      <td>1</td>\n",
       "      <td>39a</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1.1.60-39A-b12-1.205.I</td>\n",
       "      <td>07:56:54</td>\n",
       "      <td>07:56:54</td>\n",
       "      <td>8240DB007159</td>\n",
       "      <td>4</td>\n",
       "      <td>60-39A-b12-1</td>\n",
       "      <td>1</td>\n",
       "      <td>60-39A-b12-1.205.I</td>\n",
       "      <td>1</td>\n",
       "      <td>39a</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1.1.60-39A-b12-1.205.I</td>\n",
       "      <td>07:57:13</td>\n",
       "      <td>07:57:13</td>\n",
       "      <td>8240DB007388</td>\n",
       "      <td>5</td>\n",
       "      <td>60-39A-b12-1</td>\n",
       "      <td>1</td>\n",
       "      <td>60-39A-b12-1.205.I</td>\n",
       "      <td>1</td>\n",
       "      <td>39a</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                  trip_id arrival_time departure_time       stop_id  \\\n",
       "0  1.1.60-39A-b12-1.205.I     07:55:00       07:55:00  8240DB007158   \n",
       "1  1.1.60-39A-b12-1.205.I     07:55:43       07:55:43  8240DB007048   \n",
       "2  1.1.60-39A-b12-1.205.I     07:56:27       07:56:27  8240DB007699   \n",
       "3  1.1.60-39A-b12-1.205.I     07:56:54       07:56:54  8240DB007159   \n",
       "4  1.1.60-39A-b12-1.205.I     07:57:13       07:57:13  8240DB007388   \n",
       "\n",
       "   stop_sequence      route_id service_id            shape_id  direction_id  \\\n",
       "0              1  60-39A-b12-1          1  60-39A-b12-1.205.I             1   \n",
       "1              2  60-39A-b12-1          1  60-39A-b12-1.205.I             1   \n",
       "2              3  60-39A-b12-1          1  60-39A-b12-1.205.I             1   \n",
       "3              4  60-39A-b12-1          1  60-39A-b12-1.205.I             1   \n",
       "4              5  60-39A-b12-1          1  60-39A-b12-1.205.I             1   \n",
       "\n",
       "  route_short_name  \n",
       "0              39a  \n",
       "1              39a  \n",
       "2              39a  \n",
       "3              39a  \n",
       "4              39a  "
      ]
     },
     "execution_count": 145,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 146,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(1573337, 10)"
      ]
     },
     "execution_count": 146,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 147,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\Mesel\\Anaconda3\\lib\\site-packages\\ipykernel_launcher.py:1: UserWarning: Boolean Series key will be reindexed to match DataFrame index.\n",
      "  \"\"\"Entry point for launching an IPython kernel.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>trip_id</th>\n",
       "      <th>arrival_time</th>\n",
       "      <th>departure_time</th>\n",
       "      <th>stop_id</th>\n",
       "      <th>stop_sequence</th>\n",
       "      <th>route_id</th>\n",
       "      <th>service_id</th>\n",
       "      <th>shape_id</th>\n",
       "      <th>direction_id</th>\n",
       "      <th>route_short_name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>464087</th>\n",
       "      <td>10964.y1001.60-155-d12-1.94.I</td>\n",
       "      <td>06:00:00</td>\n",
       "      <td>06:00:00</td>\n",
       "      <td>8350DB004168</td>\n",
       "      <td>1</td>\n",
       "      <td>60-155-d12-1</td>\n",
       "      <td>y1001</td>\n",
       "      <td>60-155-d12-1.94.I</td>\n",
       "      <td>1</td>\n",
       "      <td>155</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                              trip_id arrival_time departure_time  \\\n",
       "464087  10964.y1001.60-155-d12-1.94.I     06:00:00       06:00:00   \n",
       "\n",
       "             stop_id  stop_sequence      route_id service_id  \\\n",
       "464087  8350DB004168              1  60-155-d12-1      y1001   \n",
       "\n",
       "                 shape_id  direction_id route_short_name  \n",
       "464087  60-155-d12-1.94.I             1              155  "
      ]
     },
     "execution_count": 147,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df[df['trip_id'] == '10964.y1001.60-155-d12-1.94.I'][df['stop_sequence']==df['stop_sequence'].min()]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 148,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\Mesel\\Anaconda3\\lib\\site-packages\\ipykernel_launcher.py:1: UserWarning: Boolean Series key will be reindexed to match DataFrame index.\n",
      "  \"\"\"Entry point for launching an IPython kernel.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>trip_id</th>\n",
       "      <th>arrival_time</th>\n",
       "      <th>departure_time</th>\n",
       "      <th>stop_id</th>\n",
       "      <th>stop_sequence</th>\n",
       "      <th>route_id</th>\n",
       "      <th>service_id</th>\n",
       "      <th>shape_id</th>\n",
       "      <th>direction_id</th>\n",
       "      <th>route_short_name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "Empty DataFrame\n",
       "Columns: [trip_id, arrival_time, departure_time, stop_id, stop_sequence, route_id, service_id, shape_id, direction_id, route_short_name]\n",
       "Index: []"
      ]
     },
     "execution_count": 148,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df[df['trip_id'] == '10964.y1001.60-155-d12-1.94.I'][df['stop_sequence']==df['stop_sequence'].max()]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 149,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "1"
      ]
     },
     "execution_count": 149,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df[df['trip_id'] == '10964.y1001.60-155-d12-1.94.I']['stop_sequence'].min()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 150,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'06:53:45'"
      ]
     },
     "execution_count": 150,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_trip = df[df['trip_id'] == '10964.y1001.60-155-d12-1.94.I']\n",
    "df_trip_max = df_trip[df_trip['stop_sequence'] == df_trip['stop_sequence'].max()]['arrival_time'].iloc[0]\n",
    "df_trip_min = df_trip[df_trip['stop_sequence'] == df_trip['stop_sequence'].min()]['arrival_time'].iloc[0]\n",
    "df_trip_max"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 152,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "538"
      ]
     },
     "execution_count": 152,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(df[df['route_short_name'] == '1']['trip_id'].unique())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 153,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>trip_id</th>\n",
       "      <th>arrival_time</th>\n",
       "      <th>departure_time</th>\n",
       "      <th>stop_id</th>\n",
       "      <th>stop_sequence</th>\n",
       "      <th>route_id</th>\n",
       "      <th>service_id</th>\n",
       "      <th>shape_id</th>\n",
       "      <th>direction_id</th>\n",
       "      <th>route_short_name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1.1.60-39A-b12-1.205.I</td>\n",
       "      <td>07:55:00</td>\n",
       "      <td>07:55:00</td>\n",
       "      <td>8240DB007158</td>\n",
       "      <td>1</td>\n",
       "      <td>60-39A-b12-1</td>\n",
       "      <td>1</td>\n",
       "      <td>60-39A-b12-1.205.I</td>\n",
       "      <td>1</td>\n",
       "      <td>39a</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1.1.60-39A-b12-1.205.I</td>\n",
       "      <td>07:55:43</td>\n",
       "      <td>07:55:43</td>\n",
       "      <td>8240DB007048</td>\n",
       "      <td>2</td>\n",
       "      <td>60-39A-b12-1</td>\n",
       "      <td>1</td>\n",
       "      <td>60-39A-b12-1.205.I</td>\n",
       "      <td>1</td>\n",
       "      <td>39a</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1.1.60-39A-b12-1.205.I</td>\n",
       "      <td>07:56:27</td>\n",
       "      <td>07:56:27</td>\n",
       "      <td>8240DB007699</td>\n",
       "      <td>3</td>\n",
       "      <td>60-39A-b12-1</td>\n",
       "      <td>1</td>\n",
       "      <td>60-39A-b12-1.205.I</td>\n",
       "      <td>1</td>\n",
       "      <td>39a</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1.1.60-39A-b12-1.205.I</td>\n",
       "      <td>07:56:54</td>\n",
       "      <td>07:56:54</td>\n",
       "      <td>8240DB007159</td>\n",
       "      <td>4</td>\n",
       "      <td>60-39A-b12-1</td>\n",
       "      <td>1</td>\n",
       "      <td>60-39A-b12-1.205.I</td>\n",
       "      <td>1</td>\n",
       "      <td>39a</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1.1.60-39A-b12-1.205.I</td>\n",
       "      <td>07:57:13</td>\n",
       "      <td>07:57:13</td>\n",
       "      <td>8240DB007388</td>\n",
       "      <td>5</td>\n",
       "      <td>60-39A-b12-1</td>\n",
       "      <td>1</td>\n",
       "      <td>60-39A-b12-1.205.I</td>\n",
       "      <td>1</td>\n",
       "      <td>39a</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                  trip_id arrival_time departure_time       stop_id  \\\n",
       "0  1.1.60-39A-b12-1.205.I     07:55:00       07:55:00  8240DB007158   \n",
       "1  1.1.60-39A-b12-1.205.I     07:55:43       07:55:43  8240DB007048   \n",
       "2  1.1.60-39A-b12-1.205.I     07:56:27       07:56:27  8240DB007699   \n",
       "3  1.1.60-39A-b12-1.205.I     07:56:54       07:56:54  8240DB007159   \n",
       "4  1.1.60-39A-b12-1.205.I     07:57:13       07:57:13  8240DB007388   \n",
       "\n",
       "   stop_sequence      route_id service_id            shape_id  direction_id  \\\n",
       "0              1  60-39A-b12-1          1  60-39A-b12-1.205.I             1   \n",
       "1              2  60-39A-b12-1          1  60-39A-b12-1.205.I             1   \n",
       "2              3  60-39A-b12-1          1  60-39A-b12-1.205.I             1   \n",
       "3              4  60-39A-b12-1          1  60-39A-b12-1.205.I             1   \n",
       "4              5  60-39A-b12-1          1  60-39A-b12-1.205.I             1   \n",
       "\n",
       "  route_short_name  \n",
       "0              39a  \n",
       "1              39a  \n",
       "2              39a  \n",
       "3              39a  \n",
       "4              39a  "
      ]
     },
     "execution_count": 153,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 154,
   "metadata": {},
   "outputs": [],
   "source": [
    "grouped_df = df.groupby('trip_id')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 155,
   "metadata": {},
   "outputs": [],
   "source": [
    "trip_start_time = grouped_df['arrival_time'].min()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 156,
   "metadata": {},
   "outputs": [],
   "source": [
    "trip_end_time = grouped_df['arrival_time'].max()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 157,
   "metadata": {},
   "outputs": [],
   "source": [
    "start_stop = grouped_df['stop_sequence'].min()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 158,
   "metadata": {},
   "outputs": [],
   "source": [
    "trip_start_time_seconds = trip_start_time.str.split(':')\n",
    "trip_end_time_seconds = trip_end_time.str.split(':')\n",
    "\n",
    "for i,time in enumerate(trip_start_time_seconds):\n",
    "    seconds = 60*60*int(time[0]) + 60*int(time[1]) + int(time[2])\n",
    "    trip_start_time_seconds.iloc[i] = seconds\n",
    "\n",
    "for i,time in enumerate(trip_end_time_seconds):\n",
    "    seconds = 60*60*int(time[0]) + 60*int(time[1]) + int(time[2])\n",
    "    trip_end_time_seconds.iloc[i] = seconds\n",
    "    \n",
    "trip_start_time_seconds = trip_start_time_seconds.astype('int64')\n",
    "trip_end_time_seconds = trip_start_time_seconds.astype('int64')\n",
    "\n",
    "trip_length = trip_end_time_seconds - trip_start_time_seconds"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 159,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_trip = pd.DataFrame({'start_time':trip_start_time,'end_time':trip_end_time,'trip_length':trip_length,'stop_sequence':start_stop})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 160,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>start_time</th>\n",
       "      <th>end_time</th>\n",
       "      <th>trip_length</th>\n",
       "      <th>stop_sequence</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>trip_id</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1.1.60-39A-b12-1.205.I</th>\n",
       "      <td>07:55:00</td>\n",
       "      <td>09:04:31</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1.y1002.60-39A-d12-1.204.I</th>\n",
       "      <td>07:55:00</td>\n",
       "      <td>09:05:41</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10.1.60-151-b12-1.90.I</th>\n",
       "      <td>08:40:00</td>\n",
       "      <td>09:35:14</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10.y1002.60-151-d12-1.89.I</th>\n",
       "      <td>08:40:00</td>\n",
       "      <td>09:34:37</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>100.1.60-151-b12-1.89.O</th>\n",
       "      <td>20:30:00</td>\n",
       "      <td>21:14:33</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                           start_time  end_time  trip_length  stop_sequence\n",
       "trip_id                                                                    \n",
       "1.1.60-39A-b12-1.205.I       07:55:00  09:04:31            0              1\n",
       "1.y1002.60-39A-d12-1.204.I   07:55:00  09:05:41            0              1\n",
       "10.1.60-151-b12-1.90.I       08:40:00  09:35:14            0              1\n",
       "10.y1002.60-151-d12-1.89.I   08:40:00  09:34:37            0              1\n",
       "100.1.60-151-b12-1.89.O      20:30:00  21:14:33            0              1"
      ]
     },
     "execution_count": 160,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_trip.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 161,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(27529, 4)"
      ]
     },
     "execution_count": 161,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_trip.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 162,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_trip = df_trip.merge(df,on=['trip_id','stop_sequence'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 163,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>trip_id</th>\n",
       "      <th>start_time</th>\n",
       "      <th>end_time</th>\n",
       "      <th>trip_length</th>\n",
       "      <th>stop_sequence</th>\n",
       "      <th>arrival_time</th>\n",
       "      <th>departure_time</th>\n",
       "      <th>stop_id</th>\n",
       "      <th>route_id</th>\n",
       "      <th>service_id</th>\n",
       "      <th>shape_id</th>\n",
       "      <th>direction_id</th>\n",
       "      <th>route_short_name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1.1.60-39A-b12-1.205.I</td>\n",
       "      <td>07:55:00</td>\n",
       "      <td>09:04:31</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>07:55:00</td>\n",
       "      <td>07:55:00</td>\n",
       "      <td>8240DB007158</td>\n",
       "      <td>60-39A-b12-1</td>\n",
       "      <td>1</td>\n",
       "      <td>60-39A-b12-1.205.I</td>\n",
       "      <td>1</td>\n",
       "      <td>39a</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1.y1002.60-39A-d12-1.204.I</td>\n",
       "      <td>07:55:00</td>\n",
       "      <td>09:05:41</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>07:55:00</td>\n",
       "      <td>07:55:00</td>\n",
       "      <td>8240DB007158</td>\n",
       "      <td>60-39A-d12-1</td>\n",
       "      <td>y1002</td>\n",
       "      <td>60-39A-d12-1.204.I</td>\n",
       "      <td>1</td>\n",
       "      <td>39a</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>10.1.60-151-b12-1.90.I</td>\n",
       "      <td>08:40:00</td>\n",
       "      <td>09:35:14</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>08:40:00</td>\n",
       "      <td>08:40:00</td>\n",
       "      <td>8230DB004606</td>\n",
       "      <td>60-151-b12-1</td>\n",
       "      <td>1</td>\n",
       "      <td>60-151-b12-1.90.I</td>\n",
       "      <td>1</td>\n",
       "      <td>151</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>10.y1002.60-151-d12-1.89.I</td>\n",
       "      <td>08:40:00</td>\n",
       "      <td>09:34:37</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>08:40:00</td>\n",
       "      <td>08:40:00</td>\n",
       "      <td>8230DB004606</td>\n",
       "      <td>60-151-d12-1</td>\n",
       "      <td>y1002</td>\n",
       "      <td>60-151-d12-1.89.I</td>\n",
       "      <td>1</td>\n",
       "      <td>151</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>100.1.60-151-b12-1.89.O</td>\n",
       "      <td>20:30:00</td>\n",
       "      <td>21:14:33</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>20:30:00</td>\n",
       "      <td>20:30:00</td>\n",
       "      <td>8220DB007514</td>\n",
       "      <td>60-151-b12-1</td>\n",
       "      <td>1</td>\n",
       "      <td>60-151-b12-1.89.O</td>\n",
       "      <td>0</td>\n",
       "      <td>151</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                      trip_id start_time  end_time  trip_length  \\\n",
       "0      1.1.60-39A-b12-1.205.I   07:55:00  09:04:31            0   \n",
       "1  1.y1002.60-39A-d12-1.204.I   07:55:00  09:05:41            0   \n",
       "2      10.1.60-151-b12-1.90.I   08:40:00  09:35:14            0   \n",
       "3  10.y1002.60-151-d12-1.89.I   08:40:00  09:34:37            0   \n",
       "4     100.1.60-151-b12-1.89.O   20:30:00  21:14:33            0   \n",
       "\n",
       "   stop_sequence arrival_time departure_time       stop_id      route_id  \\\n",
       "0              1     07:55:00       07:55:00  8240DB007158  60-39A-b12-1   \n",
       "1              1     07:55:00       07:55:00  8240DB007158  60-39A-d12-1   \n",
       "2              1     08:40:00       08:40:00  8230DB004606  60-151-b12-1   \n",
       "3              1     08:40:00       08:40:00  8230DB004606  60-151-d12-1   \n",
       "4              1     20:30:00       20:30:00  8220DB007514  60-151-b12-1   \n",
       "\n",
       "  service_id            shape_id  direction_id route_short_name  \n",
       "0          1  60-39A-b12-1.205.I             1              39a  \n",
       "1      y1002  60-39A-d12-1.204.I             1              39a  \n",
       "2          1   60-151-b12-1.90.I             1              151  \n",
       "3      y1002   60-151-d12-1.89.I             1              151  \n",
       "4          1   60-151-b12-1.89.O             0              151  "
      ]
     },
     "execution_count": 163,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_trip.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 164,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_trip.drop(['stop_sequence','arrival_time','departure_time','stop_id','route_id','shape_id'],axis=1,inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 165,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>trip_id</th>\n",
       "      <th>start_time</th>\n",
       "      <th>end_time</th>\n",
       "      <th>trip_length</th>\n",
       "      <th>service_id</th>\n",
       "      <th>direction_id</th>\n",
       "      <th>route_short_name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1.1.60-39A-b12-1.205.I</td>\n",
       "      <td>07:55:00</td>\n",
       "      <td>09:04:31</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>39a</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1.y1002.60-39A-d12-1.204.I</td>\n",
       "      <td>07:55:00</td>\n",
       "      <td>09:05:41</td>\n",
       "      <td>0</td>\n",
       "      <td>y1002</td>\n",
       "      <td>1</td>\n",
       "      <td>39a</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>10.1.60-151-b12-1.90.I</td>\n",
       "      <td>08:40:00</td>\n",
       "      <td>09:35:14</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>151</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>10.y1002.60-151-d12-1.89.I</td>\n",
       "      <td>08:40:00</td>\n",
       "      <td>09:34:37</td>\n",
       "      <td>0</td>\n",
       "      <td>y1002</td>\n",
       "      <td>1</td>\n",
       "      <td>151</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>100.1.60-151-b12-1.89.O</td>\n",
       "      <td>20:30:00</td>\n",
       "      <td>21:14:33</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>151</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                      trip_id start_time  end_time  trip_length service_id  \\\n",
       "0      1.1.60-39A-b12-1.205.I   07:55:00  09:04:31            0          1   \n",
       "1  1.y1002.60-39A-d12-1.204.I   07:55:00  09:05:41            0      y1002   \n",
       "2      10.1.60-151-b12-1.90.I   08:40:00  09:35:14            0          1   \n",
       "3  10.y1002.60-151-d12-1.89.I   08:40:00  09:34:37            0      y1002   \n",
       "4     100.1.60-151-b12-1.89.O   20:30:00  21:14:33            0          1   \n",
       "\n",
       "   direction_id route_short_name  \n",
       "0             1              39a  \n",
       "1             1              39a  \n",
       "2             1              151  \n",
       "3             1              151  \n",
       "4             0              151  "
      ]
     },
     "execution_count": 165,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_trip.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 166,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(['1', 'y1002', '3', 'y1001', 'y1003'], dtype=object)"
      ]
     },
     "execution_count": 166,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_trip[df_trip['service_id'] == "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 182,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\Mesel\\Anaconda3\\lib\\site-packages\\ipykernel_launcher.py:1: UserWarning: Boolean Series key will be reindexed to match DataFrame index.\n",
      "  \"\"\"Entry point for launching an IPython kernel.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>trip_id</th>\n",
       "      <th>start_time</th>\n",
       "      <th>end_time</th>\n",
       "      <th>trip_length</th>\n",
       "      <th>service_id</th>\n",
       "      <th>direction_id</th>\n",
       "      <th>route_short_name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>16571</th>\n",
       "      <td>5067.1.60-46A-b12-1.47.O</td>\n",
       "      <td>05:15:00</td>\n",
       "      <td>05:40:24</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>46a</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19123</th>\n",
       "      <td>6215.1.60-46A-b12-1.46.I</td>\n",
       "      <td>06:06:00</td>\n",
       "      <td>06:55:59</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>46a</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19329</th>\n",
       "      <td>6308.1.60-46A-b12-1.49.O</td>\n",
       "      <td>06:06:00</td>\n",
       "      <td>06:56:08</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>46a</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19173</th>\n",
       "      <td>6238.1.60-46A-b12-1.46.I</td>\n",
       "      <td>06:15:00</td>\n",
       "      <td>07:04:59</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>46a</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19349</th>\n",
       "      <td>6317.1.60-46A-b12-1.49.O</td>\n",
       "      <td>06:15:00</td>\n",
       "      <td>07:05:08</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>46a</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19501</th>\n",
       "      <td>6386.1.60-46A-b12-1.45.I</td>\n",
       "      <td>23:00:00</td>\n",
       "      <td>23:27:16</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>46a</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19527</th>\n",
       "      <td>6398.1.60-46A-b12-1.45.I</td>\n",
       "      <td>23:15:00</td>\n",
       "      <td>23:42:16</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>46a</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19217</th>\n",
       "      <td>6258.1.60-46A-b12-1.49.O</td>\n",
       "      <td>23:15:00</td>\n",
       "      <td>23:58:11</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>46a</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19607</th>\n",
       "      <td>6433.1.60-46A-b12-1.45.I</td>\n",
       "      <td>23:30:00</td>\n",
       "      <td>23:57:16</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>46a</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19273</th>\n",
       "      <td>6283.1.60-46A-b12-1.48.O</td>\n",
       "      <td>23:30:00</td>\n",
       "      <td>23:39:25</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>46a</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>218 rows × 7 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                        trip_id start_time  end_time  trip_length service_id  \\\n",
       "16571  5067.1.60-46A-b12-1.47.O   05:15:00  05:40:24            0          1   \n",
       "19123  6215.1.60-46A-b12-1.46.I   06:06:00  06:55:59            0          1   \n",
       "19329  6308.1.60-46A-b12-1.49.O   06:06:00  06:56:08            0          1   \n",
       "19173  6238.1.60-46A-b12-1.46.I   06:15:00  07:04:59            0          1   \n",
       "19349  6317.1.60-46A-b12-1.49.O   06:15:00  07:05:08            0          1   \n",
       "...                         ...        ...       ...          ...        ...   \n",
       "19501  6386.1.60-46A-b12-1.45.I   23:00:00  23:27:16            0          1   \n",
       "19527  6398.1.60-46A-b12-1.45.I   23:15:00  23:42:16            0          1   \n",
       "19217  6258.1.60-46A-b12-1.49.O   23:15:00  23:58:11            0          1   \n",
       "19607  6433.1.60-46A-b12-1.45.I   23:30:00  23:57:16            0          1   \n",
       "19273  6283.1.60-46A-b12-1.48.O   23:30:00  23:39:25            0          1   \n",
       "\n",
       "       direction_id route_short_name  \n",
       "16571             0              46a  \n",
       "19123             1              46a  \n",
       "19329             0              46a  \n",
       "19173             1              46a  \n",
       "19349             0              46a  \n",
       "...             ...              ...  \n",
       "19501             1              46a  \n",
       "19527             1              46a  \n",
       "19217             0              46a  \n",
       "19607             1              46a  \n",
       "19273             0              46a  \n",
       "\n",
       "[218 rows x 7 columns]"
      ]
     },
     "execution_count": 182,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_trip[df_trip['service_id'] == '1'][df_trip['route_short_name'] == '46a'].sort_values(by='start_time')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 186,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "39a ['1' 'y1002' '3' 'y1001' 'y1003']\n",
      "151 ['1' 'y1002' 'y1003' 'y1001' '3']\n",
      "65 ['1' 'y1002' 'y1003' 'y1001' '3']\n",
      "25a ['y1001' '3' 'y1003' '1' 'y1002']\n",
      "65b ['1' 'y1002' 'y1003' 'y1001' '3']\n",
      "25b ['y1001' '3' 'y1003' 'y1002' '1']\n",
      "25 ['y1001' '3' 'y1003' 'y1002' '1']\n",
      "38a ['y1001' '3' 'y1003' 'y1002' '1']\n",
      "38 ['y1001' '3' 'y1003' '1' 'y1002']\n",
      "70 ['y1001' '3' 'y1003' 'y1002' '1']\n",
      "56a ['y1001' '3' 'y1003' 'y1002' '1']\n",
      "67 ['y1001' '3' 'y1003' 'y1002' '1']\n",
      "83a ['y1001' '3' 'y1003' 'y1002' '1']\n",
      "83 ['y1001' '3' 'y1003' 'y1002' '1']\n",
      "43 ['y1001' '3' 'y1003' 'y1002' '1']\n",
      "42 ['y1001' '3' 'y1003' 'y1002' '1']\n",
      "39 ['y1001' '3' 'y1003' 'y1002' '1']\n",
      "9 ['1' 'y1002' 'y1001' '3' 'y1003']\n",
      "79 ['y1001' '3' 'y1003' 'y1002' '1']\n",
      "79a ['y1001' '3' 'y1003' 'y1002' '1']\n",
      "142 ['1' 'y1002']\n",
      "140 ['1' 'y1002' 'y1003' 'y1001' '3']\n",
      "27b ['y1001' '3' 'y1003' 'y1002' '1']\n",
      "29a ['1' 'y1002' 'y1001' '3' 'y1003']\n",
      "69 ['y1001' '3' '1' 'y1002' 'y1003']\n",
      "68 ['y1001' '3' '1' 'y1002' 'y1003']\n",
      "155 ['y1001' '3' 'y1003' 'y1002' '1']\n",
      "46a ['y1001' '3' 'y1003' 'y1002' '1']\n",
      "15 ['y1001' '3' 'y1003' 'y1002' '1']\n",
      "41 ['y1001' '3' 'y1003' 'y1002' '1']\n",
      "41c ['y1001' '3' 'y1003' 'y1002' '1']\n",
      "41b ['y1001' '3' 'y1003' 'y1002' '1']\n",
      "33 ['y1001' '3' 'y1003' 'y1002' '1']\n",
      "66 ['y1001' '3' 'y1003' 'y1002' '1']\n",
      "26 ['y1001' '3' 'y1003' 'y1002' '1']\n",
      "66b ['y1001' '3' 'y1003' 'y1002' '1']\n",
      "7d ['y1001' '3' '1' 'y1002']\n",
      "7a ['y1001' '3' '1' 'y1002' 'y1003']\n",
      "84 ['y1001' 'y1003' 'y1002' '1' '3']\n",
      "122 ['y1003' 'y1002' '1' 'y1001' '3']\n",
      "54a ['y1003' 'y1002' '1' 'y1001' '3']\n",
      "49 ['y1003' 'y1002' '1' 'y1001' '3']\n",
      "123 ['y1003' 'y1002' '1' 'y1001' '3']\n",
      "37 ['y1003' 'y1002' '1' 'y1001' '3']\n",
      "47 ['y1003' 'y1002' '1' 'y1001' '3']\n",
      "7 ['1' 'y1002' 'y1003' 'y1001' '3']\n",
      "31 ['y1003' '1' 'y1002' 'y1001' '3']\n",
      "31a ['y1003' '1' 'y1002' 'y1001' '3']\n",
      "32 ['y1003' '1' 'y1002' 'y1001' '3']\n",
      "130 ['y1003' '1' 'y1002' 'y1001' '3']\n",
      "40 ['y1003' '1' 'y1002' 'y1001' '3']\n",
      "40b ['y1003' '1' 'y1002' 'y1001' '3']\n",
      "40d ['y1003' '1' 'y1002' 'y1001' '3']\n",
      "150 ['y1003' 'y1001' '3' '1' 'y1002']\n",
      "7b ['1' 'y1002']\n",
      "27a ['y1003' 'y1002' '1' 'y1001' '3']\n",
      "44 ['y1003' 'y1002' '1' 'y1001' '3']\n",
      "145 ['y1003' 'y1002' '1' 'y1001' '3']\n",
      "61 ['y1003' 'y1002' '1' 'y1001' '3']\n",
      "14 ['y1003' 'y1002' '1' 'y1001' '3']\n",
      "14c ['y1003' 'y1002' '1' 'y1001' '3']\n",
      "27 ['y1003' 'y1002' '1' 'y1001' '3']\n",
      "77a ['y1003' 'y1002' '1' 'y1001' '3']\n",
      "15b ['y1003' 'y1002' '1' 'y1001' '3']\n",
      "15a ['y1003' 'y1002' '1' 'y1001' '3']\n",
      "120 ['y1003' 'y1002' '1' 'y1001' '3']\n",
      "53 ['y1003' 'y1002' '1' 'y1001' '3']\n",
      "1 ['y1003' 'y1002' '1' 'y1001' '3']\n",
      "68a ['1' 'y1002']\n",
      "69x ['1' 'y1002']\n",
      "16 ['y1003' 'y1002' '1' 'y1001' '3']\n",
      "16c ['y1003' 'y1002' '1' 'y1001' '3']\n",
      "4 ['y1003' 'y1002' '1' 'y1001' '3']\n",
      "40e ['y1003' 'y1002' '1' 'y1001' '3']\n",
      "66a ['y1003' 'y1002' '1' 'y1001' '3']\n",
      "13 ['y1003' 'y1002' '1' 'y1001' '3']\n",
      "11 ['y1003' 'y1002' '1' 'y1001' '3']\n",
      "31b ['1' 'y1002']\n",
      "15d ['y1002' '1']\n",
      "84a ['y1002' '1']\n",
      "84x ['y1002' '1']\n",
      "27x ['y1002' '1']\n",
      "16d ['y1002' '1']\n",
      "41x ['y1002' '1']\n",
      "33x ['y1002' '1']\n",
      "41d ['y1002' '1']\n",
      "66e ['y1002' '1']\n",
      "66x ['y1002' '1']\n",
      "38b ['y1002' '1']\n",
      "38d ['y1002' '1']\n",
      "25d ['y1002' '1']\n",
      "67x ['y1002' '1']\n",
      "33e ['y1002' '1']\n",
      "33d ['y1002']\n",
      "33b ['1']\n",
      "44b ['1' 'y1002']\n"
     ]
    }
   ],
   "source": [
    "for route in df_trip['route_short_name'].unique():\n",
    "    print(route, df_trip[df_trip['route_short_name'] == route]['service_id'].unique())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.7"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}
